﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using Corona.TagContract;
using Corona.TagServer.Exception;
using System.Text;

namespace Corona.TagServer
{   
    /// <summary>
    /// 已经测试完毕，发现多处BUG，现已全部修复
    /// </summary>
    public class CoronaDatabase:IDisposable
    {
        private DbProviderFactory dbFactory;
        private DbConnection connection;
        private string queryString;

        public CoronaDatabase()
        {
            string dbProvider;
            string connectionString;
            GetDatabaseConnectionInfo(out dbProvider, out connectionString);
            dbFactory = DbProviderFactories.GetFactory(dbProvider);
            connection = GetDatabaseConnection(connectionString);
            connection.Open();
        }

       #region IDisposable Members

        public void Close()
        {
            ((IDisposable)this).Dispose();
        }

        void IDisposable.Dispose()
        {
            DisposeConnection(true);
            GC.SuppressFinalize(this);
        }

        ~CoronaDatabase()
        {
            DisposeConnection(false);
        }

        protected virtual void DisposeConnection(bool isDisposing)
        {
            if (connection.State == ConnectionState.Open && isDisposing)
                connection.Close();
        }

        #endregion

        /// <summary>
        /// 获取下一个可用的UID，要考虑多线程的并发的情况，目前还未处理
        /// </summary>
        /// <returns>下一个可用的UID值</returns>
        public long GetNextValidUID()
        {          
            long nextValidUID=0;
            try
            {
                queryString = "insert into CoronaServer(CreateTime) values('" + DateTime.Now.ToString("u") + "')";
                ExecuteNonQuery();
                queryString = "select max(UID) from CoronaServer";
                DbDataReader resultReader = ExecuteReader();
                if (resultReader.Read())
                {
                    nextValidUID = resultReader.GetInt32(0) + 1;           
                }
                resultReader.Close();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return nextValidUID;
        }

        /// <summary>
        /// 向一个富描述中插入“一个”标签
        /// </summary>
        /// <param name="UID">待插入的富描述的UID</param>
        /// <param name="tag">待插入的标签，该标签的各个域均不能为空</param>
        public void InsertTagIntoRichDescription(long UID, Tag tag)
        {
            try
            {
                UpdateTagRelation(UID, tag);
                queryString = "insert into RichDescription(TagName,TagValue,UID) values('" + tag.Name + "','" + tag.Value + "'," + UID.ToString() + ")";
                ExecuteNonQuery();   
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
        }

        /// <summary>
        /// 获取一对标签的相关程序，对于两个标签，如果不关心标签的类型，应该设置为"*"，标签的值不能为任意
        /// </summary>
        /// <param name="tagPair">标签对</param>
        /// <returns>该对标签的相关性分数</returns>
        public long GetRelevance(Pair<Tag, Tag> tagPair)
        {
            return GetRelevance(tagPair.A, tagPair.B);
        }

        private long GetRelevance(QueryTag tag1,QueryTag tag2)
        {
            queryString = "select Frequency from TagRelation where TagName1"+tag1.Name+" and TagValue1"+tag1.Value+" and TagName2"+tag2.Name+" and TagValue2"+tag2.Value;
            long relevance = 0;
            try
            {
                DbDataReader resultReader = ExecuteReader();
                while(resultReader.Read())
                {
                    relevance += resultReader.GetInt32(0);
                }
                resultReader.Close();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return relevance;
        }

        private long GetRelevance(Tag tag1, Tag tag2)
        {
            return GetRelevance(new QueryTag(tag1), new QueryTag(tag2));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tag1"></param>
        /// <returns></returns>
        public List<Pair<Tag,long>> GetRelatedTags(Tag tag1)
        {
            QueryTag tag = new QueryTag(tag1);
            queryString = "select TagName2,TagValue2 from TagRelation where TagName1" + tag.Name + " and TagValue1" + tag.Value;
            List<Pair<Tag, long>> relatedTags = new List<Pair<Tag, long>>();
            try
            {
                DbDataReader resultReader = ExecuteReader();
                while (resultReader.Read())
                {
                    Tag tag2=new Tag(resultReader.GetString(0), resultReader.GetString(1));
                    relatedTags.Add(new Pair<Tag,long>(tag2,GetRelevance(new Pair<Tag,Tag>(tag1,tag2))));
                }
                resultReader.Close();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return relatedTags;
        }

        /// <summary>
        /// 返回指定UID的富描述的特定类型的标签所构成的富描述XML文档
        /// </summary>
        /// <param name="UID">指定富描述的UID</param>
        /// <param name="tagNames">所关心的标签类型</param>
        /// <returns>生成的富描述XML文档</returns>
        public XmlDocument GetRichDescriptionXmlDocument(long UID, string[] tagNames)
        {
            XmlDocument xml = new XmlDocument();
            XmlElement richDescription = xml.CreateElement("richdescription");
            string name=null;
            string value = null;
            richDescription.SetAttribute("uid", UID.ToString());
            xml.AppendChild(richDescription);

            IEnumerable<Tag> tags= GetTagsByUID(UID, tagNames);
            try
            {
                foreach(Tag tag in tags){
                    name = tag.Name;
                    value = tag.Value;
                    XmlElement tagNode = xml.CreateElement("tag");
                    tagNode.SetAttribute("name", name);
                    tagNode.InnerText = value;
                    richDescription.AppendChild(tagNode);
                }
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return xml;
        }

        /// <summary>
        /// 查询包含某个标签的富描述的UID
        /// </summary>
        /// <param name="tag">要查找的标签，不关心的域应该设置为"*"，标签名称和标签值都可以设置为"*"</param>
        /// <returns>符和条件的富描述的UID</returns>
        public List<long> GetUIDsByTag(QueryTag tag)
        {
            queryString = "select distinct UID from RichDescription where TagName"+tag.Name+" and TagValue"+tag.Value;
            List<long> UIDs = new List<long>();
            try
            {
                DbDataReader resultReader = ExecuteReader();
                while(resultReader.Read())
                {
                    UIDs.Add(resultReader.GetInt32(0));
                }
                resultReader.Close();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return UIDs;
        }

        /// <summary>
        /// 根据应用程序名称返回该应用的验证信息
        /// </summary>
        /// <param name="AppName">要查询的应用程序的名称，应该是唯一的</param>
        /// <returns>该应用程序的验证字串</returns>
        public string GetAppVerifyInfo(string AppName)
        {
            queryString = "select MD5 from Application where AppName='" + AppName + "'";
            string MD5 = null;
            try
            {
                DbDataReader resultReader = ExecuteReader();
                if (resultReader.Read())
                {
                    MD5 = resultReader.GetString(0);
                }
                resultReader.Close();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
            return MD5;
        }

        /// <summary>
        /// 暂用Access，未存xml
        /// </summary>
        /// <param name="registerXML"></param>
        public void RegisterApplication(string registerXML,string appName,long authority)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider getMd5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            string md5=BitConverter.ToString(getMd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(registerXML))).Replace("-", "");
            try
            {
                queryString = "select AppName from Application where AppName='" + appName + "'";
                DbDataReader resultReader = ExecuteReader();
                if (resultReader.Read()&&!resultReader.IsDBNull(0))
                {
                    queryString = "update Application set MD5='" + md5 + "', AppXML='"+"<>"+"', Authority="+authority.ToString()+" where AppName='"+appName + "'";
                }
                else
                {
                    queryString = "insert into Application(AppName,MD5,AppXML,Authority) values('" + appName + "','" + md5 + "','" + "<>" + "'," + authority + ")";
                }
                ExecuteNonQuery();
            }
            catch (DbException e)
            {
                throw new DatabaseException(e.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UID"></param>
        /// <param name="newTag"></param>
        private void UpdateTagRelation(long UID, Tag newTag)
        {
            IEnumerable<Tag> tags = GetTagsByUID(UID, null);
            foreach (Tag tag in tags)
            {
                long relation = GetRelevance(tag, newTag);
                if (relation > 0)
                {
                    relation++;
                    queryString = "update TagRelation set Frequency=" + relation.ToString() + " where TagName1='" + tag.Name + "' and TagValue1='" + tag.Value + "' and TagName2='" + newTag.Name + "' and TagValue2='" + newTag.Value + "'";
                    ExecuteNonQuery();
                    queryString = "update TagRelation set Frequency=" + relation.ToString() + " where TagName2='" + tag.Name + "' and TagValue2='" + tag.Value + "' and TagName1='" + newTag.Name + "' and TagValue1='" + newTag.Value + "'";
                    ExecuteNonQuery();
                }
                else
                {
                    relation++;
                    queryString = "insert into TagRelation(TagName1,TagValue1,TagName2,TagValue2,Frequency) values('" + tag.Name + "','" + tag.Value + "','" + newTag.Name + "','" + newTag.Value + "',"+relation.ToString()+")";
                    ExecuteNonQuery();
                    queryString = "insert into TagRelation(TagName1,TagValue1,TagName2,TagValue2,Frequency) values('" + newTag.Name + "','" + newTag.Value + "','" + tag.Name + "','" + tag.Value + "'," + relation.ToString()+")";
                    ExecuteNonQuery();
                }
            }
        }


    

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UID"></param>
        /// <param name="tagNames"></param>
        /// <returns></returns>
        private IEnumerable<Tag> GetTagsByUID(long UID, string[] tagNames)
        {
            List<Tag> tags = new List<Tag>();
            queryString = "select TagName,TagValue from RichDescription where UID=" + UID.ToString();
            if (tagNames != null && tagNames.Length > 0)
            {
                queryString += " and (TagName='" + tagNames[0] + "'";
                for (long i = 1; i < tagNames.Length; i++)
                {
                    queryString += " or TagName='" + tagNames[i] + "'";
                }
                queryString += ")";
            }
            DbDataReader resultReader = ExecuteReader();
            while (resultReader.Read())
            {
                tags.Add(new Tag(resultReader.GetString(0), resultReader.GetString(1)));
            }
            resultReader.Close();

            return tags;
        }

        /// <summary>
        /// 从配置文件中读取数据库的连接信息
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connectionString">数据库的连接串</param>
        private void GetDatabaseConnectionInfo(out string dbProvider, out string connectionString)
        {
            XmlDocument xml = new XmlDocument();
            xml.Load(CoronaConfig.CONFIGRATION);
            dbProvider = null;
            connectionString = null;
            XmlNode databaseConfig = xml.SelectSingleNode("CoronaDatabaseConnection");
            foreach (XmlNode innerNode in databaseConfig)
            {
                if (innerNode.Name == "providerName")
                    dbProvider = innerNode.InnerText;
                if (innerNode.Name == "connectionString")
                    connectionString = innerNode.InnerText;
            }
        }

        /// <summary>
        /// 建立并返回数据库的连接
        /// </summary>
        /// <param name="connectionString">待连接数据库的连接串</param>
        /// <returns>数据库的连接</returns>
        private DbConnection GetDatabaseConnection(string connectionString)
        {
            DbConnection connection = dbFactory.CreateConnection();
            connection.ConnectionString = connectionString;
            return connection;
        }
    
        /// <summary>
        /// 在已连接的数据库中执行一条SQL命令，该命令不返回结果，例如insert、update等等
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        private void ExecuteNonQuery()
        {
            DbCommand query = GetDbCommand();
            query.CommandText = queryString;
            query.ExecuteNonQuery();
        }

        /// <summary>
        /// 在已连接的数据库中执行一条SQL命令，并返回该语句返回结果的reader
        /// </summary>
        /// <param name="queryString">SQL命令字符串</param>
        /// <returns>查询结果读取器</returns>
        private DbDataReader ExecuteReader()
        {
            DbCommand query = GetDbCommand();
            query.CommandText = queryString;
            DbDataReader resultReader = query.ExecuteReader();
            return resultReader;
        }

        /// <summary>
        /// 数据库命令工厂函数
        /// </summary>
        /// <returns>一个空的数据库命令</returns>
        private DbCommand GetDbCommand()
        {
            DbCommand command = dbFactory.CreateCommand();
            command.Connection = connection;
            return command;
        }
    }
}